IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.ScriptCheckConstraintTable'))
BEGIN
	DROP PROCEDURE MD3.ScriptCheckConstraintTable
END
GO

CREATE Procedure MD3.ScriptCheckConstraintTable

AS  
BEGIN

/*

	Generates a SQL script that can be saved to 03CheckConstraints, in MasterData format, that is used as the basis for all Check constraint manipulation.



*/


PRINT '/*'
PRINT '	This script holds the MasterData about our CHECK Constraints'
PRINT '	Simply modify this metadata and rerun the MD3 deployment and any CHECK constraint changes'
PRINT '		will be reflected in your database.  '
PRINT ''
PRINT '	To add a new check constraint...Add a new INSERT line to the file.'
PRINT '	To modify a check constraint...Modify the values of the EXISTING check constraint'
PRINT '	To delete a check constraint...Set the DropConstraint flag to 1.  Do not actually delete the row.'
PRINT ''
PRINT ' This script can be automatically regenerated by running MD3.ScriptCheckConstraintTable'
PRINT '*/'

SET NOCOUNT ON
PRINT 'SET NOCOUNT ON'
PRINT '--remove all old entries from table'
PRINT 'TRUNCATE TABLE MD3.CheckConstraints'
PRINT ''
PRINT '--add new entries to table'

DECLARE @exec_str VARCHAR(MAX), @new_str VARCHAR(MAX), @quote VARCHAR(MAX) = '''';
SELECT @exec_str = 'INSERT INTO MD3.CheckConstraints (TableName, SchemaName, ConstraintName, ConstraintExpression, ColumnName, AllowNoCheck, DropConstraint) VALUES('

DECLARE 
	@TableName varchar(256)
	,@SchemaName varchar(256)
	,@ConstraintName varchar(256)
	,@CheckConstraintExpression varchar(max)
	,@ColumnName varchar(256)
	,@AllowNoCheck	bit
	,@DropConstraint varchar(256)

DECLARE dave CURSOR FOR 
	SELECT 
		TableName, SchemaName, ConstraintName, ConstraintExpression, ColumnName, AllowNoCheck, DropConstraint 
	FROM MD3.CheckConstraints
	ORDER BY SchemaName, TableName
OPEN dave
FETCH NEXT FROM dave INTO 
	@TableName
	,@SchemaName 
	,@ConstraintName 
	,@CheckConstraintExpression 
	,@ColumnName
	,@AllowNoCheck
	,@DropConstraint 
WHILE (@@FETCH_STATUS = 0)
BEGIN

	SELECT @new_str = @exec_str + '''' + @TableName + ''',''' + @SchemaName + ''',''' + @ConstraintName + ''',''' + REPLACE (@CheckConstraintExpression,'''','''''') + ''',''' + @ColumnName + ''','
	SELECT @new_str += CONVERT(VARCHAR(1),@AllowNoCheck) + ',' + @DropConstraint +');'
	PRINT @new_str
	
	
	FETCH NEXT FROM dave INTO 
		@TableName
		,@SchemaName 
		,@ConstraintName 
		,@CheckConstraintExpression 
		,@ColumnName
		,@AllowNoCheck
		,@DropConstraint 
END;
CLOSE dave
DEALLOCATE dave


PRINT '--now building CHECK Constraints'
PRINT 'EXEC MD3.RunCheckConstraints'
PRINT 'GO'
PRINT 'SET NOCOUNT OFF'

END
GO
